package com.sreManager.service;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.sreManager.mapper.CompanyBuildingMapper;
import com.sreManager.mapper.CompanyEstimateMapper;
import com.sreManager.mapper.CompanyInchargeMapper;

@Service
public class UploadService {

	@Autowired CompanyBuildingMapper companyBuildingMapper;
	@Autowired CompanyInchargeMapper companyInchargeMapper;
	@Autowired CompanyEstimateMapper companyEstimateMapper;

	/**
	 * 업로드 시설, 담당자, 측정내역 등록
	 * @param param
	 * @return
	 */
	public List<Map<String, Object>> insertEstimates(List<Map<String, Object>> param) {
		/*
		 * 중복데이터의 기준은 명칭으로 정함
		 * 시설		: 시설명이 같으면 중복(excel_1)
		 * 담당자	: 무조건 저장
		 * 측정		: 무조건 저장
		 *
		 * 코드		: T	[성공]			True
		 * 			: F [실패]			False
		 * 			: E [미존재]			Empty
		 * 			: D [중복]			Duplication
		 * 			: U [갱신]			Update
		 * 			: VE [유효성 오류]	ValidationError
		 * 				: N [숫자 미할당]		Number
		 * 				: R [범위 초과]		Range
		 * 				: L [길이 초과]		Length
		 */

		for(int i = 0 ; i < param.size() ; i++){
			try{
				Map<String, Object> building = null;
				Map<String, Object> incharge1 = null;
				Map<String, Object> estimate1 = null;
				Map<String, Object> estimate2 = null;
				Map<String, Object> estimate3 = null;

				String buildingCd = null;

				//기본값 실패로 셋팅
				param.get(i).put("result_building", "F");
				param.get(i).put("result_incharge", "F");
				param.get(i).put("result_measure1", "F");
				param.get(i).put("result_measure2", "F");
				param.get(i).put("result_measure3", "F");

				//사업자 등록번호 / 시설명 미기입시, 무조건 실패
				if(!param.get(i).containsKey("excel_1") || "".equals(param.get(i).get("excel_1"))){
					continue;
				}

				building = this.convertExcelToBuilding(param.get(i));

				//시설검색
				List<Map<String, Object>> existBuildingList = companyBuildingMapper.selectExistBuilding(building);

				//시설 존재여부 체크 (1)
				//case 1-1 : 존재시 UPDATE
				if(existBuildingList != null && existBuildingList.size() > 0){
					buildingCd = existBuildingList.get(0).get("building_cd").toString();
					building.put("buildingCd", buildingCd);

					if(companyBuildingMapper.updateBuildingMap(building) == 1){
						param.get(i).put("result_building", "U");
					}
					else{
						param.get(i).put("result_building", "F");
					}
				}
				//case 1-2 : 미존재시 INSERT
				else{
					if(companyBuildingMapper.insertBuildingMap(building) == 1){
						buildingCd = building.get("buildingCd").toString();
						param.get(i).put("result_building", "T");
					}
					else{
						param.get(i).put("result_building", "F");
					}
				}

				//시설처리 성공시
				if(buildingCd != null || "".equals(buildingCd)){
					//담당자 기입여부 체크
					if(param.get(i).containsKey("excel_13") && !"".equals(param.get(i).get("excel_13"))){
						incharge1 = this.convertExcelToIncharge(param.get(i), buildingCd, 0);
						//담당자 존재여부 체크 (2)
						List<Map<String, Object>> existInchargeList = companyInchargeMapper.selectExistIncharge(incharge1);
						//case 2-1 : 존재시 UPDATE
						if(existInchargeList != null && existInchargeList.size() > 0){
							incharge1.put("inchargeCd", existInchargeList.get(0).get("incharge_cd").toString());
							if(companyInchargeMapper.updateInchargeMap(incharge1) == 1){
								param.get(i).put("result_incharge", "U");
							}
							else{
								param.get(i).put("result_incharge", "F");
							}
						}
						//case 2-2 : 미존재시 INSERT
						else{
							if(companyInchargeMapper.insertInchargeMap(incharge1) == 1){
								param.get(i).put("result_incharge", "T");
							}
							else{
								param.get(i).put("result_incharge", "F");
							}
						}
					}
					else{
						param.get(i).put("result_incharge", "E");
					}

					//측정1 기입여부 확인
					if(param.get(i).containsKey("excel_21") && !"".equals(param.get(i).get("excel_21"))
							&& param.get(i).containsKey("excel_23") && !"".equals(param.get(i).get("excel_23"))
							&& param.get(i).containsKey("excel_24") && !"".equals(param.get(i).get("excel_24"))){
						estimate1 = this.convertExcelToEstimate(param.get(i), buildingCd, 0);
						//측정정보 존재여부 체크 (3)
						List<Map<String, Object>> existEstimateList = companyEstimateMapper.selectExistEstimate(estimate1);
						//case 3-1 : 존재시 UPDATE
						if(existEstimateList != null && existEstimateList.size() > 0){
							estimate1.put("estimateCd", existEstimateList.get(0).get("estimate_cd").toString());
							if(companyEstimateMapper.updateEstimateMap(estimate1) == 1){
								param.get(i).put("result_measure1", "U");
							}
							else{
								param.get(i).put("result_measure1", "F");
							}
						}
						//case 3-2 : 미존재시 INSERT
						else{
							if(companyEstimateMapper.insertEstimateMap(estimate1) == 1){
								param.get(i).put("result_measure1", "T");
							}
							else{
								param.get(i).put("result_measure1", "F");
							}
						}
					}
					else{
						param.get(i).put("result_measure1", "E");
					}

					//측정2 기입여부 확인
					if(param.get(i).containsKey("excel_27") && !"".equals(param.get(i).get("excel_27"))
							&& param.get(i).containsKey("excel_29") && !"".equals(param.get(i).get("excel_29"))
							&& param.get(i).containsKey("excel_30") && !"".equals(param.get(i).get("excel_30"))){
						estimate2 = this.convertExcelToEstimate(param.get(i), buildingCd, 1);
						//측정정보 존재여부 체크 (4)
						List<Map<String, Object>> existEstimateList = companyEstimateMapper.selectExistEstimate(estimate2);
						//case 4-1 : 존재시 UPDATE
						if(existEstimateList != null && existEstimateList.size() > 0){
							estimate2.put("estimateCd", existEstimateList.get(0).get("estimate_cd").toString());
							if(companyEstimateMapper.updateEstimateMap(estimate2) == 1){
								param.get(i).put("result_measure2", "U");
							}
							else{
								param.get(i).put("result_measure2", "F");
							}
						}
						//case 4-2 : 미존재시 INSERT
						else{
							if(companyEstimateMapper.insertEstimateMap(estimate2) == 1){
								param.get(i).put("result_measure2", "T");
							}
							else{
								param.get(i).put("result_measure2", "F");
							}
						}
					}
					else{
						param.get(i).put("result_measure2", "E");
					}

					//측정3 기입여부 확인
					if(param.get(i).containsKey("excel_33") && !"".equals(param.get(i).get("excel_33"))
							&& param.get(i).containsKey("excel_35") && !"".equals(param.get(i).get("excel_35"))
							&& param.get(i).containsKey("excel_36") && !"".equals(param.get(i).get("excel_36"))){
						estimate3 = this.convertExcelToEstimate(param.get(i), buildingCd, 2);
						//측정정보 존재여부 체크 (5)
						List<Map<String, Object>> existEstimateList = companyEstimateMapper.selectExistEstimate(estimate3);
						//case 5-1 : 존재시 UPDATE
						if(existEstimateList != null && existEstimateList.size() > 0){
							estimate3.put("estimateCd", existEstimateList.get(0).get("estimate_cd").toString());
							if(companyEstimateMapper.updateEstimateMap(estimate3) == 1){
								param.get(i).put("result_measure3", "U");
							}
							else{
								param.get(i).put("result_measure3", "F");
							}
						}
						//case 5-2 : 미존재시 INSERT
						else{
							if(companyEstimateMapper.insertEstimateMap(estimate3) == 1){
								param.get(i).put("result_measure3", "T");
							}
							else{
								param.get(i).put("result_measure3", "F");
							}
						}
					}
					else{
						param.get(i).put("result_measure3", "E");
					}
				}
				else{
					continue;
				}
			} catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
			} finally {
				//시설, 담당자1, 담당자2, 견적1, 견적2 결과코드 확인
				String[] resultKey = {"result_building", "result_incharge", "result_measure1", "result_measure2", "result_measure3"};
				for(int j = 0 ; j < resultKey.length ; j++){
					if(!param.get(i).containsKey(resultKey[j]) || "".equals(param.get(i).containsKey(resultKey[j]))){
						param.get(i).put(resultKey[j], "F");
					}
				}
			}
		}
		return param;
	}

	private Map<String, Object> convertExcelToCompany(Map<String, Object> param){
		String[] columns = { "businessDivision", "companyNumber", "companyName",
							"companyPresident", "businessAddr", "businessType",
							"businessConditions" };
		Map<String, Object> result = new HashMap<String, Object>();
		for(int i = 0 ; i < columns.length ; i++){
			if(param.containsKey("excel_" + i)){
				result.put(columns[i], param.get("excel_" + i));
			}
			else{
				result.put(columns[i], null);
			}
		}
		return result;
	}

	//엑셀데이터 -> 시설 데이터 추출
	private Map<String, Object> convertExcelToBuilding(Map<String, Object> param){
		String[] columns = { "measureTarget", "buildingName", "buildingAddr"
				, "buildingNewAddr", "buildingZipcode", "buildingMemo"
				, "companyNumber", "companyName", "companyPresident"
				, "businessAddr", "businessConditions", "businessType", "companyEmail"};
		String[] excels = { "0", "1", "2"
				, "3", "4", "5"
				, "6", "7", "8"
				, "9", "10", "11"
				, "12" };
		Map<String, Object> result = new HashMap<String, Object>();
		for(int i = 0 ; i < columns.length ; i++){
			if(param.containsKey("excel_" + excels[i])){
				result.put(columns[i], param.get("excel_" + excels[i]));
			}
			else{
				result.put(columns[i], null);
			}
		}
		return result;
	}
	//엑셀데이터 -> 담당자 데이터 추출
	private Map<String, Object> convertExcelToIncharge(Map<String, Object> param, String buildingCd, int order){
		String[] columns = { "inchargeName", "inchargeDepartment", "inchargeRank"
				,"inchargeTel", "inchargePhone", "inchargeFax"
				,"inchargeEmail", "inchargeMemo" };
		String[] excels = { "13", "14", "15", "16", "17", "18", "19", "20" };

		Map<String, Object> result = new HashMap<String, Object>();
		result.put("buildingCd", buildingCd);
		for(int i = 0 ; i < columns.length ; i++){
			if(param.containsKey("excel_" + excels[i])){
				result.put(columns[i], param.get("excel_" + excels[i]));
			}
			else{
				result.put(columns[i], null);
			}
		}
		return result;
	}
	//엑셀데이터 -> 측정 데이터 추출
	private Map<String, Object> convertExcelToEstimate(Map<String, Object> param, String buildingCd, int order){
		String[] columns = { "estimatDate", "measureDate", "measureType"
				, "estimatSum", "estimatSurtax", "estimatMemo" };
		String[][] excels = {{ "21", "22", "23", "24", "25", "26" }
				,{ "27", "28", "29", "30", "31", "32" }
				,{ "33", "34", "35", "36", "37", "38" }};

		Map<String, Object> result = new HashMap<String, Object>();
		result.put("buildingCd", buildingCd);
		for(int i = 0 ; i < columns.length ; i++){
			if(param.containsKey("excel_" + excels[order][i])){
				if(i == 3){
					result.put(columns[i], param.get("excel_" + excels[order][i]).toString().replaceAll(",", ""));
				}
				else{
					result.put(columns[i], param.get("excel_" + excels[order][i]));
				}
			}
			else{
				result.put(columns[i], null);
			}
		}
		return result;
	}
}
